import pymysql
from dbutils.pooled_db import PooledDB
from typing import List, Dict, Any, Tuple, Optional, Union
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class MySQLPool:
    def __init__(self, host: str, port: int, user: str, password: str, 
                 database: str, charset: str = 'utf8mb4', pool_size: int = 5):
        """
        初始化MySQL数据库连接池
        
        Args:
            host: 数据库主机地址
            port: 数据库端口
            user: 用户名
            password: 密码（确保是字符串类型）
            database: 数据库名
            charset: 字符集，默认为utf8mb4
            pool_size: 连接池大小，默认为5
        """
        try:
            # 确保密码是字符串类型
            if not isinstance(password, str):
                password = str(password)
            
            # 记录连接参数（隐藏密码）
            #logger.info(f"尝试连接数据库: {user}@{host}:{port}/{database}")
            
            self.pool = PooledDB(
                creator=pymysql,
                maxconnections=pool_size,
                mincached=2,
                maxcached=5,
                blocking=True,
                host=str(host),  # 确保所有参数都是字符串
                port=int(port),  # 确保端口是整数
                user=str(user),
                password=password,
                database=str(database),
                charset=str(charset),
                autocommit=True,
                cursorclass=pymysql.cursors.DictCursor
            )
            
            # 测试连接是否成功
            self._test_connection()
            #logger.info("数据库连接池创建成功")
            
        except Exception as e:
            error_msg = f"数据库连接池创建失败: {str(e)}"
            logger.error(error_msg)
            raise ConnectionError(error_msg)
    
    def _test_connection(self):
        """测试数据库连接是否正常"""
        try:
            conn = self.pool.connection()
            with conn.cursor() as cursor:
                cursor.execute("SELECT 1")
            conn.close()
        except Exception as e:
            raise ConnectionError(f"数据库连接测试失败: {str(e)}")
    
    def execute_sql(self, sql: str, params: Optional[Union[Tuple, List, Dict]] = None) -> Dict[str, Any]:
        """
        执行SQL语句（INSERT, UPDATE, DELETE等）
        """
        result = {
            'status': 'success',
            'affected_rows': 0,
            'lastrowid': None,
            'error_message': None
        }
        
        conn = None
        cursor = None
        
        try:
            conn = self.pool.connection()
            cursor = conn.cursor()
            
            if params is not None:
                # 确保所有参数都是正确的类型
                processed_params = self._process_params(params)
                affected_rows = cursor.execute(sql, processed_params)
            else:
                affected_rows = cursor.execute(sql)
                
            conn.commit()
            result['affected_rows'] = affected_rows
            result['lastrowid'] = cursor.lastrowid
            
        except Exception as e:
            if conn:
                conn.rollback()
            result['status'] = 'error'
            result['error_message'] = str(e)
            logger.error(f"SQL执行错误: {sql}, 错误: {e}")
            
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
                
        return result
    
    def _process_params(self, params: Union[Tuple, List, Dict]) -> Union[Tuple, List, Dict]:
        """处理参数，确保所有值都是正确的类型"""
        if isinstance(params, dict):
            return {k: self._convert_param(v) for k, v in params.items()}
        elif isinstance(params, (list, tuple)):
            return type(params)(self._convert_param(v) for v in params)
        else:
            return (self._convert_param(params),)
    
    def _convert_param(self, value: Any) -> Any:
        """转换参数值为合适的类型"""
        if value is None:
            return None
        elif isinstance(value, (str, int, float, bool)):
            return value
        else:
            # 将其他类型转换为字符串
            return str(value)
    
    def query(self, sql: str, params: Optional[Union[Tuple, List, Dict]] = None) -> List[Dict[str, Any]]:
        """
        查询数据库，返回字典列表
        """
        conn = None
        cursor = None
        result = []
        
        try:
            conn = self.pool.connection()
            cursor = conn.cursor()
            
            if params is not None:
                processed_params = self._process_params(params)
                cursor.execute(sql, processed_params)
            else:
                cursor.execute(sql)
                
            result = cursor.fetchall()
            
        except Exception as e:
            logger.error(f"查询错误: {sql}, 错误: {e}")
            raise Exception(f"查询执行错误: {str(e)}")
            
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
        if len(result) == 0:
            result = []
        return result
    
    def query_one(self, sql: str, params: Optional[Union[Tuple, List, Dict]] = None) -> Optional[Dict[str, Any]]:
        """查询单条记录"""
        result = self.query(sql, params)
        return result[0] if result else None
    
    def close_pool(self):
        """关闭连接池"""
        if hasattr(self, 'pool'):
            self.pool.close()
            #logger.info("数据库连接池已关闭")
    
    def __del__(self):
        self.close_pool()

# 测试函数
def test_connection():
    """测试数据库连接"""
    # 请根据你的实际配置修改这些参数
    test_configs = [
        {
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'password': '',  # 空密码
            'database': 'test'
        },
        {
            'host': '127.0.0.1',
            'port': 3306,
            'user': 'root',
            'password': 'root',  # 常见测试密码
            'database': 'mysql'  # 尝试连接系统数据库
        }
    ]
    
    for i, config in enumerate(test_configs, 1):
        try:
            print(f"\n尝试连接配置 {i}:")
            print(f"主机: {config['host']}, 端口: {config['port']}")
            print(f"用户: {config['user']}, 数据库: {config['database']}")
            
            db_pool = MySQLPool(**config)
            print("✅ 连接成功!")
            
            # 测试查询
            result = db_pool.query("SELECT VERSION() as version")
            print(f"MySQL版本: {result[0]['version']}")
            
            db_pool.close_pool()
            return config  # 返回成功的配置
            
        except ConnectionError as e:
            print(f"❌ 连接失败: {e}")
        except Exception as e:
            print(f"❌ 其他错误: {e}")
    
    return None

# 使用示例
if __name__ == "__main__":
    # 首先测试连接
    successful_config = test_connection()
    
    if successful_config:
        print(f"\n使用成功配置创建连接池...")
        try:
            db_pool = MySQLPool(**successful_config)
            
            # 示例操作
            # 1. 创建测试表
            create_table_sql = """
            CREATE TABLE IF NOT EXISTS test_table (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100),
                value TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
            result = db_pool.execute_sql(create_table_sql)
            print(f"创建表结果: {result}")
            
            # 2. 插入数据
            insert_sql = "INSERT INTO test_table (name, value) VALUES (%s, %s)"
            insert_result = db_pool.execute_sql(insert_sql, ("test", "hello world"))
            print(f"插入结果: {insert_result}")
            
            # 3. 查询数据
            query_result = db_pool.query("SELECT * FROM test_table")
            print(f"查询结果: {query_result}")
            
            db_pool.close_pool()
            
        except Exception as e:
            print(f"操作失败: {e}")
    else:
        print("\n所有连接尝试都失败了，请检查：")
        print("1. MySQL服务是否启动")
        print("2. 连接参数是否正确")
        print("3. 用户是否有权限访问数据库")
        print("4. 防火墙设置")
